SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE static_search_4
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL AS

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
-- Run these three searches and pay attention to the execution time.
-- Then run again with plans enabled and check them.
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_4 @orderid = 11000
PRINT 'Search on order ID ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_4 @status = 'N'
PRINT 'Searching for new orders ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_4 @prodid = 76
PRINT 'Search on product ID ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
-- Compare the plans for these two calls. They use the same parameters,
-- but customer ERNTC is the customer with the highest numbers of orders
-- in the database. BOLSR. on the other hand, has a single order.
EXEC static_search_4 @custid = 'ERNTC',
                     @fromdate = '19980218', @todate = '19980218'
EXEC static_search_4 @custid = 'BOLSR',
                     @fromdate = '19980101', @todate = '19981231'
